package dao.impl;

import dao.BaseDao;
import dao.DormroomsDao;
import entity.Dormrooms;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DormroomsDaoImpl extends BaseDao implements DormroomsDao {

    @Override
    public List<Dormrooms> cx2(Integer lid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dormrooms> dormRooms = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT room_id, building_id, room_number, floor, capacity " +
                            "FROM dorm_rooms WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND room_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();

            while (rs.next()) {
                Dormrooms room = new Dormrooms();
                room.setRoom_id(rs.getInt("room_id"));
                room.setBuilding_id(rs.getInt("building_id"));
                room.setRoom_number(rs.getString("room_number"));
                room.setFloor(rs.getInt("floor"));
                room.setCapacity(rs.getInt("capacity"));
                dormRooms.add(room);
            }
            return dormRooms;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Dormrooms> cxb(Integer lid, int page, int pageSize) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dormrooms> dormRooms = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT room_id, building_id, room_number, floor, capacity " +
                            "FROM dorm_rooms WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND room_id = ?");
            }
            sql.append(" LIMIT ?, ?");

            pst = conn.prepareStatement(sql.toString());
            int paramIndex = 1;

            if (lid != null && lid > 0) {
                pst.setInt(paramIndex++, lid);
            }

            int startPage=page*pageSize;
            pst.setInt(paramIndex++, startPage);
            pst.setInt(paramIndex, pageSize);

            rs = pst.executeQuery();

            while (rs.next()) {
                Dormrooms room = new Dormrooms();
                room.setRoom_id(rs.getInt("room_id"));
                room.setBuilding_id(rs.getInt("building_id"));
                room.setRoom_number(rs.getString("room_number"));
                room.setFloor(rs.getInt("floor"));
                room.setCapacity(rs.getInt("capacity"));
                dormRooms.add(room);
            }
            return dormRooms;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int getTotalCount(Integer lid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM dorm_rooms WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND room_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int scb(int id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "DELETE FROM dorm_rooms WHERE room_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int xgb(int room_id, int building_id, String room_number, int floor, int capacity)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "UPDATE dorm_rooms SET building_id = ?, room_number = ?, " +
                    "floor = ?, capacity = ? WHERE room_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, building_id);
            pst.setString(2, room_number);
            pst.setInt(3, floor);
            pst.setInt(4, capacity);
            pst.setInt(5, room_id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int tjb(int building_id, String room_number, int floor, int capacity)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "INSERT INTO dorm_rooms(building_id, room_number, floor, capacity) " +
                    "VALUES(?, ?, ?, ?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, building_id);
            pst.setString(2, room_number);
            pst.setInt(3, floor);
            pst.setInt(4, capacity);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }
}